package Persistencia;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

import DAO.DAOCliente;
import Modelo.Cliente;
import Modelo.Combinado;
import Modelo.Descuento;
import Modelo.EstrategiaCosto;
import Modelo.PorDia;
import Modelo.PorKm;
import Modelo.Usuario;


public class AdministracionPersistenciaCliente{
	
	private static AdministracionPersistenciaCliente instance;
	
	private AdministracionPersistenciaCliente(){
		
	}
	 
	public static AdministracionPersistenciaCliente getInstance(){
		if(instance==null)
			instance = new AdministracionPersistenciaCliente();
		return instance;
	}
	 	

public void insert(Usuario usuario){
	Connection con = PoolConnection.getPoolConnection().getConnection();
	try{
		PreparedStatement ps = con.prepareStatement("insert into dbo.usuarios1 values (?,?,?,?,?,?,?,?,?,?, ?)");
		ps.setInt(1, usuario.getDni());
		ps.setString(2, usuario.getNombre());
		ps.setString(3, usuario.getApellido());
		ps.setString (4, usuario.getNacimiento());
		ps.setString(5, usuario.getDomicilio());
		ps.setString (6,usuario.getTelefono());
		ps.setString(7, usuario.getSexo());
		ps.setString(8, usuario.getNacionalidad());
		ps.setString(9, usuario.getUsuario());
		ps.setString(10, usuario.getPassword());
		ps.setString(11, "A");
	
		ps.execute();
		PoolConnection.getPoolConnection().closeConnections();
	}catch(Exception e){
		System.out.println("Error en insert cliente "+e.getMessage());
	}
}

public void update(Cliente p){
	Connection con = PoolConnection.getPoolConnection().getConnection();
	try{
		PreparedStatement ps = con.prepareStatement("" +
				"update dbo.usuarios1 set nombre = ?, apellido = ?, nacimiento = ?, domicilio = ?, telefono =? ,sexo = ?, nacionalidad = ? , usuario =?, password=?, estado=? " +
				"where dni = ?");
		ps.setString(1, p.getNombre());
		ps.setString(2, p.getApellido());
		ps.setString (3, p.getNacimiento());
		ps.setString(4, p.getDomicilio());
		ps.setString (5,p.getTelefono());
		ps.setString(6, p.getSexo());
		ps.setString(7, p.getNacionalidad());
		ps.setString(8, p.getUsuario());
		ps.setString(9, p.getPassword());
		ps.setString(10, p.getEstado());		
		ps.setInt(11, p.getDni());
		ps.execute();
		PoolConnection.getPoolConnection().closeConnections();
	}catch(Exception e){
		System.out.println("Error en update cliente "+e.getMessage());
	}
}

	public ArrayList<Cliente> select(){
		ArrayList<Cliente> result = new ArrayList<Cliente>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
	
		try{
			String query = "SELECT * FROM dbo.usuarios1 "; 
			
			PreparedStatement ps = con.prepareStatement(query);
			//ps.setString(1,descripcion);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Cliente rec = new Cliente(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11));	
				result.add(rec);				
			}
			
		
			PoolConnection.getPoolConnection().closeConnections();
		}catch(Exception e){
			System.err.print("Error al Buscar Clientes");
			PoolConnection.getPoolConnection().closeConnections();
		}
		
		return result;
	}
	
	public Cliente select(int dni){
		ArrayList<Cliente> result = new ArrayList<Cliente>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
	
		try{
			String query = "SELECT * FROM dbo.usuarios1 where dni = ?"; 
			
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1,dni);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Cliente rec = new Cliente(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11));	
				result.add(rec);				
			}
			
		
			PoolConnection.getPoolConnection().closeConnections();
		}catch(Exception e){
			System.err.print("Error al Buscar Clientes");
			PoolConnection.getPoolConnection().closeConnections();
		}
		
		return result.get(0);
	}

public void delete(Usuario p){
	Connection con = PoolConnection.getPoolConnection().getConnection();
	try{
					
		PreparedStatement ps2 = con.prepareStatement("delete from dbo.usuarios1 where dni = ?");
		ps2.setInt(1, p.getDni());
		ps2.execute();
		
		PoolConnection.getPoolConnection().closeConnections();
	}catch(Exception e){
		System.out.println("Error en delete cliente "+e.getMessage());
	}
}

}